<?php
class Database {
    private $host;
    private $user;
    private $pass;
    private $dbname;
    private $conn = null;
    private const RUB_EXCHANGE_RATE = 12; // 设置卢布汇率
    
    public function __construct($host, $user, $pass, $dbname) {
        $this->host = $host;
        $this->user = $user;
        $this->pass = $pass;
        $this->dbname = $dbname;
    }
    
    public function getConnection() {
        if ($this->conn === null || !$this->conn->ping()) {
            $this->conn = new mysqli($this->host, $this->user, $this->pass, $this->dbname);
            if ($this->conn->connect_error) {
                throw new Exception("数据库连接失败: " . $this->conn->connect_error);
            }
            $this->conn->set_charset("utf8mb4");
        }
        return $this->conn;
    }
    
    public function close() {
        if ($this->conn !== null) {
            $this->conn->close();
            $this->conn = null;
        }
    }
    
    public function saveCategory($categorys) {
        try {
            foreach($categorys as $category) {
                $now = date('Y-m-d H:i:s');
                // 插入新分类
                $sql = "INSERT INTO category 
                        (title, parent_id, created_at, updated_at, is_sync, `order`) 
                        VALUES (?, ?, ?, ?, 0, 0)";
                $stmt = $this->conn->prepare($sql);
                if (!$stmt) {
                    echo "预处理插入SQL失败: " . $this->conn->error . "\n";
                    return false;
                }
                // 先将值存储在变量中
                $title = $category['chineseName'];
                $parentId = $category['parentId'] ?? 0 ;
                $createdAt = $now;
                $updatedAt = $now;
                $is_sync = 0;
                $stmt->bind_param('siss',
                    $title,
                    $parentId,
                    $createdAt,
                    $updatedAt);
                
                
                $result = $stmt->execute();
                if (!$result) {
                    echo "执行SQL失败: " . $stmt->error . "\n";
                }
                
                $stmt->close();
            }
            return true;
            
        } catch (Exception $e) {
            echo "保存分类出错: " . $e->getMessage() . "\n";
            return false;
        }
    }
    
    public function saveProduct($product, $categoryId) {
        $conn = $this->getConnection();
        if (!$conn) {
            throw new Exception("数据库连接失败");
        }

        // 开始事务
        $conn->begin_transaction();

        try {
            // 检查商品是否已存在
            $checkSql = "SELECT id FROM products WHERE offerId = ?";
            $checkStmt = $conn->prepare($checkSql);
            if (!$checkStmt) {
                throw new Exception("预处理检查SQL失败: " . $conn->error);
            }
            
            $checkStmt->bind_param('s', $product['offerId']);
            $checkStmt->execute();
            $result = $checkStmt->get_result();
            $existingProduct = $result->fetch_assoc();
            $checkStmt->close();

            if ($existingProduct) {
                echo "商品 {$product['offerId']} 已存在，跳过\n";
                $conn->rollback();
                return false;
            }

            // 准备数据
            $title = $product['subjectTrans'] ?? $product['subject'] ?? '';
            $goodsDetail = $product['description'] ?? '';
            // 计算商品价格（乘以汇率）
            $price = number_format(($product['priceInfo']['price'] ?? 0) * self::RUB_EXCHANGE_RATE, 2, '.', '');
            $now = date('Y-m-d H:i:s');
            
            // 处理商品图片
            $image = $product['imageUrl'] ?? ''; // 封面图
            $pollImages = ''; // 轮播图
            if (isset($product['productImage']['images']) && is_array($product['productImage']['images'])) {
                $pollImages = implode(',', $product['productImage']['images']);
            }

            // 处理SKU数据
            $skuJson = '{"attrs":{},"sku":[]}';
            if (isset($product['productSkuInfos']) && !empty($product['productSkuInfos'])) {
                // 1. 收集所有规格名和规格值
                $attrs = [];
                foreach ($product['productSkuInfos'] as $sku) {
                    if (isset($sku['skuAttributes'])) {
                        foreach ($sku['skuAttributes'] as $attr) {
                            // 使用翻译后的属性名和值
                            $attrName = $attr['attributeNameTrans'] ?? $attr['attributeName'] ?? '';
                            $attrValue = $attr['valueTrans'] ?? $attr['value'] ?? '';
                            
                            if (!empty($attrName) && !empty($attrValue)) {
                                if (!isset($attrs[$attrName])) {
                                    $attrs[$attrName] = [];
                                }
                                if (!in_array($attrValue, $attrs[$attrName])) {
                                    $attrs[$attrName][] = $attrValue;
                                }
                            }
                        }
                    }
                }
                
                // 保存SKU属性到sku_attribute表
                foreach ($attrs as $attrName => $attrValues) {
                    $this->saveSkuAttribute($attrName, $attrValues);
                }
                
                // 2. 构建SKU数组
                $skuList = [];
                foreach ($product['productSkuInfos'] as $sku) {
                    $skuItem = [];
                    if (isset($sku['skuAttributes'])) {
                        foreach ($sku['skuAttributes'] as $attr) {
                            // 使用翻译后的属性名和值
                            $attrName = $attr['attributeNameTrans'] ?? $attr['attributeName'] ?? '';
                            $attrValue = $attr['valueTrans'] ?? $attr['value'] ?? '';
                            
                            if (!empty($attrName) && !empty($attrValue)) {
                                $skuItem[$attrName] = $attrValue;
                            }
                        }
                    }
                    // 添加其他SKU信息
                    $skuItem['stock'] = (string)($sku['amountOnSale'] ?? 0);
                    $skuItem['price'] = (string)(($sku['price'] ?? 0) * self::RUB_EXCHANGE_RATE);
                    $skuItem['kan_num'] = "0";
                    $skuItem['kan_money'] = "0";
                    $skuList[] = $skuItem;
                }
                
                // 3. 构建最终的SKU JSON结构
                $skuData = [
                    'attrs' => $attrs,
                    'sku' => $skuList
                ];
                $skuJson = json_encode($skuData, JSON_UNESCAPED_UNICODE);
            }

            // 插入新商品
            $sql = "INSERT INTO products (
                offerId, name, goods_detail, price, category_ids, sku, is_processed, 
                image, poll_images, created_at, updated_at
            ) VALUES (?, ?, ?, ?, ?, ?, 1, ?, ?, ?, ?)";
            $stmt = $conn->prepare($sql);
            if (!$stmt) {
                throw new Exception("预处理插入SQL失败: " . $conn->error);
            }
            $stmt->bind_param('sssdssssss', 
                $product['offerId'],
                $title, 
                $goodsDetail, 
                $price, 
                $categoryId, 
                $skuJson, 
                $image,
                $pollImages,
                $now,
                $now
            );

            if (!$stmt->execute()) {
                throw new Exception("执行SQL失败: " . $stmt->error);
            }

            $productId = $stmt->insert_id;
            $stmt->close();

            // 保存SKU详情
            if (isset($product['productSkuInfos']) && !empty($product['productSkuInfos'])) {
                $skuSql = "INSERT INTO products_sku_detail (
                    products_id, sku_text_json, sku_name, sku_num, stock, price, sku_text,
                    sales, kan_num, kan_money, created_at, updated_at, image
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                $skuStmt = $conn->prepare($skuSql);
                if (!$skuStmt) {
                    throw new Exception("预处理插入SKU SQL失败: " . $conn->error);
                }

                $skuNum = 0;
                foreach ($product['productSkuInfos'] as $sku) {
                    $skuTextValues = [];
                    
                    if (isset($sku['skuAttributes'])) {
                        foreach ($sku['skuAttributes'] as $attr) {
                            $attrValue = $attr['valueTrans'] ?? $attr['value'] ?? '';
                            if (!empty($attrValue)) {
                                $skuTextValues[] = $attrValue;
                            }
                        }
                    }
                    
                    // 构建SKU文本JSON（数组格式）
                    $skuTextJson = json_encode($skuTextValues, JSON_UNESCAPED_UNICODE);
                    // 获取最后一个规格值作为sku_name
                    $skuName = end($skuTextValues);
                    $stock = $sku['amountOnSale'] ?? 0;
                    $skuPrice = number_format(($sku['price'] ?? 0) * self::RUB_EXCHANGE_RATE, 2, '.', '');
                    $skuText = implode(',', $skuTextValues);
                    $sales = 0;
                    $kanNum = 0;
                    $kanMoney = "0.00";
                    $skuImage = $sku['skuImageUrl'] ?? '';

                    $skuStmt->bind_param('issiissiissss', 
                        $productId,
                        $skuTextJson,
                        $skuName,
                        $skuNum,
                        $stock,
                        $skuPrice,
                        $skuText,
                        $sales,
                        $kanNum,
                        $kanMoney,
                        $now,
                        $now,
                        $skuImage
                    );

                    if (!$skuStmt->execute()) {
                        throw new Exception("保存SKU详情失败: " . $skuStmt->error);
                    }
                    $skuNum++;
                }
                $skuStmt->close();
            }

            // 提交事务
            $conn->commit();
            return true;

        } catch (Exception $e) {
            $conn->rollback();
            throw $e;
        }
    }
    
    public function saveSku($sku, $productId) {
        try {
            $sql = "INSERT INTO lb_goods_sku (
                    goods_id, key_name, specs, shop_price, 
                    market_price, cost_price, stock, sku_sn, update_time
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) 
                ON DUPLICATE KEY UPDATE 
                    shop_price=VALUES(shop_price),
                    market_price=VALUES(market_price),
                    cost_price=VALUES(cost_price),
                    stock=VALUES(stock),
                    update_time=VALUES(update_time)";
            
            $specs = json_encode($sku['properties']);
            $update_time = time();
            
            $stmt = $this->conn->prepare($sql);
            if (!$stmt) {
                throw new Exception("预处理SKU SQL失败: " . $this->conn->error);
            }
            
            $stmt->bind_param('issdddisi',
                $productId,
                $sku['specName'],
                $specs,
                $sku['price'],
                $sku['marketPrice'],
                $sku['costPrice'],
                $sku['stock'],
                $sku['skuCode'],
                $update_time
            );
            
            if (!$stmt->execute()) {
                throw new Exception("保存SKU失败: " . $stmt->error);
            }
            
            $stmt->close();
            return true;
            
        } catch (Exception $e) {
            echo "保存SKU出错: " . $e->getMessage() . "\n";
            return false;
        }
    }
    
    public function getLevel3Categories() {
        $sql = "SELECT id FROM lb_goods_category WHERE level = 3";
        return $this->conn->query($sql);
    }

    public function saveSkuAttribute($attrName, $attrValues) {
        try {
            $conn = $this->getConnection();
            // 先检查是否存在
            $checkSql = "SELECT id, attr_value FROM sku_attribute WHERE attr_name = ?";
            $checkStmt = $conn->prepare($checkSql);
            if (!$checkStmt) {
                throw new Exception("预处理检查SKU属性SQL失败: " . $conn->error);
            }
            
            $checkStmt->bind_param('s', $attrName);
            $checkStmt->execute();
            $result = $checkStmt->get_result();
            $existingAttr = $result->fetch_assoc();
            $checkStmt->close();
            
            $now = date('Y-m-d H:i:s');
            
            // 合并现有值和新值
            if ($existingAttr) {
                $existingValues = json_decode($existingAttr['attr_value'], true);
                if (!is_array($existingValues)) {
                    $existingValues = [];
                }
                $newValues = array_values(array_unique(array_merge($existingValues, $attrValues)));
                sort($newValues); // 对值进行排序
                $attrValuesJson = json_encode($newValues, JSON_UNESCAPED_UNICODE);
                
                // 更新已存在的属性
                $sql = "UPDATE sku_attribute 
                       SET attr_value = ?,
                           updated_at = ?
                       WHERE attr_name = ?";
                           
                $stmt = $conn->prepare($sql);
                if (!$stmt) {
                    throw new Exception("预处理更新SKU属性SQL失败: " . $conn->error);
                }
                
                $stmt->bind_param('sss',
                    $attrValuesJson,
                    $now,
                    $attrName
                );
            } else {
                // 插入新属性
                sort($attrValues); // 对值进行排序
                $attrValuesJson = json_encode(array_values($attrValues), JSON_UNESCAPED_UNICODE);
                $sql = "INSERT INTO sku_attribute (
                    attr_name, attr_type, attr_value, sort, created_at, updated_at
                ) VALUES (?, 'checkbox', ?, 0, ?, ?)";
                
                $stmt = $conn->prepare($sql);
                if (!$stmt) {
                    throw new Exception("预处理插入SKU属性SQL失败: " . $conn->error);
                }
                
                $stmt->bind_param('ssss',
                    $attrName,
                    $attrValuesJson,
                    $now,
                    $now
                );
            }
            
            if (!$stmt->execute()) {
                throw new Exception("保存SKU属性失败: " . $stmt->error);
            }
            
            $stmt->close();
            return true;
        } catch (Exception $e) {
            echo "保存SKU属性出错: " . $e->getMessage() . "\n";
            return false;
        }
    }

    private function getSubCategories($keyword) {
        try {
            // 构建请求参数
            $params = [
                'appKey' => $this->appKey,
                'appSecret' => $this->appSecret,
                'accessToken' => $this->accessToken,
                'keyword' => $keyword,
                'pageSize' => 100
            ];
            
            // 调用1688 API获取子分类
            $url = "https://gw.open.1688.com/openapi/param2/1/com.alibaba.p4p/alibaba.cross.categoryInfo/";
            $response = $this->makeRequest($url, $params);
            
            if (!$response) {
                echo "获取子分类失败: 无响应\n";
                return [];
            }
            
            $data = json_decode($response, true);
            if (!isset($data['result']) || empty($data['result'])) {
                echo "获取子分类失败: 无数据\n";
                return [];
            }
            
            // 处理返回的子分类数据
            $subCategories = [];
            foreach ($data['result'] as $item) {
                $subCategories[] = [
                    'categoryID' => $item['categoryId'],
                    'chineseName' => $item['categoryName'],
                    'parentCateId' => 0 // 这里设置为0，因为我们是根据关键字获取的
                ];
            }
            
            return $subCategories;
            
        } catch (Exception $e) {
            echo "获取子分类出错: " . $e->getMessage() . "\n";
            return [];
        }
    }
    
    public function crawlCategories() {
        try {
            // 定义目标分类
            $targetCategories = [
                '男装' => '100000000',
                '女装' => '100000001',
                '童装' => '100000002',
                '玩具' => '100000003',
                '小家电' => '100000004'
            ];
            
            $allCategories = [];
            
            // 直接使用目标分类作为一级分类
            foreach ($targetCategories as $name => $id) {
                $allCategories[] = [
                    'categoryID' => $id,
                    'chineseName' => $name,
                    'parentCateId' => 0
                ];
                
                // 使用分类名称作为关键字获取二级分类
                $subCategories = $this->getSubCategories($name);
                if ($subCategories) {
                    $allCategories = array_merge($allCategories, $subCategories);
                }
                
                // 添加延迟，避免频率限制
                sleep(2);
            }
            
            // 保存分类到数据库
            if (!empty($allCategories)) {
                $this->saveCategory($allCategories);
            }
            
            return $allCategories;
            
        } catch (Exception $e) {
            echo "获取分类出错: " . $e->getMessage() . "\n";
            return false;
        }
    }
} 